

ALTER           proc sp_create_update_diebank_lot_cavium (
  
  @part_number varchar(50), 
  @lot_number varchar(50), 
  @qty_in int,
  @qty_out int,
  @date_in datetime,
  @date_out datetime,
  @inventory_type varchar(50),
  @vendor_name varchar(50),
  @date_code varchar(50),
  @vendor_lot varchar(50),
  @lot_comments varchar(1000))
  
  as
  begin
  
  	declare @parent_prd_id int
  	declare @base_part_number varchar(50)
  	declare @process_step_id int
  	declare @location_id int
  	declare @int int
  	declare @normal_cost float
  	declare @lot_id int
  
 	-- Commented by KumaraSwamy on 20th August, 2004.
 	/*if ( ( @vendor_lot is null ) OR ( @vendor_lot = '' ) )
 	begin
 		set @vendor_lot = @lot_number 
         end*/
  	--select @parent_prd_id = P_Parent_Product_ID from product where part_number like @part_number + '%'
  	--select @base_part_number = part_number from product where product_id = @parent_prd_id
  	select @process_step_id = Process_Step.ProcessStep_ID from product, Process_Step where PS_Type_Name like @inventory_type and product.Part_Number like @part_number+'%' and product.ProcessStep_ID = Process_Step.ProcessStep_ID 
  
  	if @process_step_id is null -- new Product or Product Name mismatch
  	begin
  		-- dummy update to avoid SQLException
  		update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
  		return
  	end
  
  	select @location_id = Location_ID from Location where L_Type = @inventory_type and L_Vendor_Name = @vendor_name
  	select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
  
  	print 'Lot_ID = '+convert(varchar, @lot_id)
  	-->>>>---- START -- Update 'TRANSIT' lots to 'Close' --->>>>-----
  
  	declare @previous_processStep_id int
  	declare @previous_processStep_type varchar(50)
  	declare @previous_processStep_type_name varchar(50)
  
  	declare @tempCursor CURSOR
  
  	set @tempCursor = CURSOR for
  	select ProcessStep_ID, PS_Type, Process_Type_Name from fn_get_process_flow_by_id (@process_step_id)
  			
  	open @tempCursor
  	fetch next from @tempCursor
  	into @previous_processStep_id, @previous_processStep_type, @previous_processStep_type_name
  
  	while @@fetch_status = 0
  	begin
  		update 	Lot_Table set Lot_Current_Status = 'Close' where ProcessStep_ID = @previous_processStep_id and Lot_Number = @lot_number	and Lot_Current_Status = 'TRANSIT'
  		
  		fetch next from @tempCursor
  		into @previous_processStep_id, @previous_processStep_type, @previous_processStep_type_name
  	end
  	close @tempCursor
  	deallocate @tempCursor
  
  	-->>>>---- END -- Update 'TRANSIT' lots to 'Close' --->>>>-----
  
  	-- Create Lot if it doesnt exist
  	if @lot_id is null
  	begin 
  		select @normal_cost = PSLT_Cost_Normal from Process_Step_Location_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id
  		insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Lot_Cost, Lot_Current_Status, Timestamp, Lot_Date_Code, Lot_Comments) values (@process_step_id, @location_id, @lot_number, (@normal_cost * @qty_in), 'Running', CURRENT_TIMESTAMP, @date_code, @lot_comments)
  		select @lot_id = @@IDENTITY
  	end
  	
  	else
  	begin
  		update Lot_Table set Lot_Current_Status = 'Running', Lot_Date_Code = @date_code, Lot_Comments = @lot_comments where Lot_ID = @lot_id
  	end	
  
  	-- insert/update Inventory Lot
  	declare @inv_lot_id int
  	select @inv_lot_id = Lot_ID from Inventory_Lot where lot_id = @lot_id
  	if @inv_lot_id is null
  	begin
  		declare @temp_qty_out int
  		if @qty_out is not null 
  		begin
  			set @temp_qty_out = @qty_out
  		end
  		else
  		begin
  			set @temp_qty_out = 0
  		end
  		--Insert into Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Out, IL_Date_In, IL_Date_Out, TIMESTAMP, IL_Vendor_Lot_Number) values (@lot_id, @qty_in - @temp_qty_out, null, @date_in, @date_out, CURRENT_TIMESTAMP, @vendor_lot)
  		--one more condition if the user enters the quantity_out = quantity_in bhaskar on 12 dec 2003
  		if @qty_in = @qty_out 
  		begin
			Insert into Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Out, IL_Date_In, IL_Date_Out, TIMESTAMP, IL_Vendor_Lot_Number) values (@lot_id, @qty_in - @temp_qty_out, null, @date_in, @date_out, CURRENT_TIMESTAMP, @vendor_lot)
  			update Lot_Table set Lot_Current_Status = 'Close', Lot_Comments = @lot_comments where Lot_ID = @lot_id
  			insert into Inventory_Lot_History (Lot_ID, IL_Packing_Method, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out, IL_Qty_Shipped, IL_Qty_Balance, timestamp, IL_Current_Status, IL_Vendor_Lot_Number) 
  			values (@lot_id, null, @date_in, @date_out, @qty_in, @qty_out, null, null, CURRENT_TIMESTAMP, 'Close', @vendor_lot)
  		end
  		else
  		begin
 			if @qty_out is not null and @qty_out > 0
 			begin
			Insert into Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Out, IL_Date_In, IL_Date_Out, TIMESTAMP, IL_Vendor_Lot_Number) values (@lot_id, @qty_in - @temp_qty_out, null, @date_in, null, CURRENT_TIMESTAMP, @vendor_lot)
  			insert into Inventory_Lot_History (Lot_ID, IL_Packing_Method, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out, IL_Qty_Shipped, IL_Qty_Balance, timestamp, IL_Current_Status, IL_Vendor_Lot_Number) 
  			values (@lot_id, null, @date_in, @date_out, @qty_in, @qty_out, null, null, CURRENT_TIMESTAMP, 'Running', @vendor_lot)
 			end
			else
			begin
				Insert into Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Out, IL_Date_In, IL_Date_Out, TIMESTAMP, IL_Vendor_Lot_Number) values (@lot_id, @qty_in , null, @date_in, null, CURRENT_TIMESTAMP, @vendor_lot)
			end
 
  			update Lot_Table set Lot_Current_Status = 'Running', Lot_Comments = @lot_comments where Lot_ID = @lot_id
  		end
  	
  
  	end
  	else
  	begin
  		if @date_out is not null-- update the lot where the qty_out and date_out vals are given
  		begin
  			declare @previous_qty int
  			declare @delta_qty int
  			select @previous_qty = IL_Qty_In from Inventory_Lot where lot_id = @lot_id
  			set @delta_qty = @previous_qty - @qty_in
			declare @max_timestamp datetime
  			--new code block written by Bhaskar on 10 December 2003 begins
  			/*select @previous_qty = IL_Qty_In from Inventory_Lot where lot_id = @lot_id
  			insert into inventory_lot_history 
  			(Lot_ID, IL_Packing_Method, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out, IL_Qty_Shipped, 
  			IL_Qty_Balance, Timestamp, IL_Current_Status)
  			values 
  			(@lot_id, NULL, @date_in, @date_out, @previous_qty, @qty_out, null, null, CURRENT_TIMESTAMP, 'RUNNING')*/
  			--new code block written by Bhaskar on 10 December 2003 ends
  			--new query is placed by Bhaskar on 10 December 2003 after commenting the previous query.
  			--update Inventory_Lot set IL_Date_In = @date_in, IL_Date_Out = @date_out, IL_Qty_In = @qty_in-@qty_out where Lot_ID = @inv_lot_id
  		--update Inventory_Lot set IL_Date_In = @date_in, IL_Date_Out = @date_out, IL_Qty_In = @qty_in-@qty_out, IL_Qty_Out = null, timestamp = CURRENT_TIMESTAMP, IL_Vendor_Lot_Number = @vendor_lot where Lot_ID = @inv_lot_id
  			--update Inventory_Lot set IL_Date_In = @date_in, IL_Date_Out = @date_out, IL_Qty_In = @qty_in, IL_Qty_Out = @delta_qty where Lot_ID = @inv_lot_id
  			if (@qty_in -@qty_out) = 0
  			begin
				update Inventory_Lot set IL_Date_In = @date_in, IL_Date_Out = @date_out, IL_Qty_In = @qty_in-@qty_out, IL_Qty_Out = null, timestamp = CURRENT_TIMESTAMP, IL_Vendor_Lot_Number = @vendor_lot where Lot_ID = @inv_lot_id
   				update lot_table set lot_current_status = 'Close', Lot_Comments = @lot_comments where lot_id = @lot_id
   				--declare @max_timestamp datetime
   				select @max_timestamp = max(timestamp) from inventory_lot_history where lot_id = @lot_id				
				UPDATE Inventory_Lot_History SET IL_Packing_Method = null, IL_Date_In = @date_in, IL_Date_Out = @date_out, IL_Qty_In = @qty_in, IL_Qty_Out = @qty_out, IL_Qty_Shipped = null, IL_Qty_Balance = null, timestamp = CURRENT_TIMESTAMP, IL_Current_Status = 'Close', IL_Vendor_Lot_Number = @vendor_lot where (Lot_ID = @inv_lot_id ) AND ( Timestamp = @max_timestamp)
   				--insert into Inventory_Lot_History (Lot_ID, IL_Packing_Method, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out, IL_Qty_Shipped, IL_Qty_Balance, timestamp, IL_Current_Status, IL_Vendor_Lot_Number) 
   				--values (@lot_id, null, @date_in, @date_out, @qty_in, @qty_out, null, null, CURRENT_TIMESTAMP, 'Close', @vendor_lot)
   				--update inventory_lot_history set il_current_status = 'Close' where lot_id = @lot_id and timestamp = @max_timestamp
   			end
  			else
  			begin
  				--declare @max_timestamp datetime
				update Inventory_Lot set IL_Date_In = @date_in, IL_Date_Out = null, IL_Qty_In = @qty_in-@qty_out, IL_Qty_Out = null, timestamp = CURRENT_TIMESTAMP, IL_Vendor_Lot_Number = @vendor_lot where Lot_ID = @inv_lot_id
  				select @max_timestamp = MAX(Timestamp) from Inventory_Lot_History where Lot_ID = @inv_lot_id and IL_Qty_In = @qty_in
  				update Inventory_Lot_History set IL_Qty_Out = @qty_out , IL_Date_Out = @date_out where (Lot_ID = @inv_lot_id ) AND ( Timestamp = @max_timestamp)
  			end
  		end
  		else-- update the lot where the qty_out and date_out vals are not given
  		begin
  			--new code block written by Bhaskar on 10 December 2003 begins
  			declare @previous_qty_in int
  			--select @previous_qty_in = IL_Qty_In from Inventory_Lot where lot_id = @lot_id
  			/*insert into inventory_lot_history 
  			(Lot_ID, IL_Packing_Method, IL_Date_In, IL_Qty_In, IL_Qty_Shipped, IL_Qty_Balance,
  			Timestamp, IL_Current_Status)
  			values 
  			(@lot_id, NULL, @date_in, @previous_qty_in, null, null, CURRENT_TIMESTAMP, 'RUNNING')*/
  			--new code block written by Bhaskar on 10 December 2003 ends
  			update Inventory_Lot set IL_Date_In = @date_in, IL_Qty_In = @qty_in, IL_Vendor_Lot_Number = @vendor_lot where Lot_ID = @inv_lot_id
  		end
  	end
  end


